<?php
/**
* checkout report class extends class.report.php
*/

class checkoutreport 
{	
	public function checkoutreport()
	{
		$this->getRates();
	}
	
	public function getStartTime()
	{
		$sql = "select datetime from `shift-transactions` where shift = 'start' order by datetime desc";
		$res = mysql_query($sql) or die(mysql_error(). $sql	);
		while(list($time) = mysql_fetch_row($res))
		{
			return $time;
		}	
	}
	
	public function getshift($date) {
		if(!$date)$date=date("Y-m-d H:i:s");
		list($d, $t) = explode(" ", $date);
		list($h, $m, $s) = explode(":", $t);
		$sql = "select shift_id from shifts where $h between shift_start and shift_end";
		$res = mysql_query($sql) or die(mysql_error() .$sql);
		list($shift)=mysql_fetch_row($res);
		
		if($h==16||$h==15)
		{
			$this->shiftnum=3;
			return $shift = "3rd";
		}
		elseif($h==8||$h==7)
		{
			$this->shiftnum=2;
			return $shift = "2nd";
		}
		$this->shiftnum=1;
	return  "1st";
	}
	
	public function getLatestShifts($shiftid)
	{
		$sql = "SELECT `shift-transaction_id`,datetime,user_id  FROM `shift-transactions` where shift = 'start' order by datetime desc ";
		$res = mysql_query($sql);
		$ret = "<select name='rblshifts' id='rblshifts' onchange='myform.submit();'>";
		$ret .= "<option value=''>&nbsp;</option>";
		while(list($shift_transaction_id,$datetime,$userid)=mysql_fetch_row($res))
		{
			if($shiftid == $shift_transaction_id)
			{
				$select = "selected";
			}else
			{
				$select = " ";
			}
			$__sql = "SELECT user_id  FROM `shift-transactions` where shift = 'end' 
			and `datetime` > '$datetime'
			order by datetime asc 
			limit 0,1";
			$__res = mysql_query($__sql);
			list($userid)=mysql_fetch_row($__res);
			$_sql = "select fullname from users where user_id = '$userid'";
			$_res = mysql_query($_sql);
			list($username)=mysql_fetch_row($_res);
			$ret .= "<option value='$shift_transaction_id' $select>$datetime - $username - ".$this->getshift($datetime)."</option>";
		}
		$ret .= "</select>";

		return $ret;
	}

	
	public function getSpecialFloorID() 
	{
		$sql = " select settings_value from settings where settings_name = 'SPECIALFLOORID' ";
		$res = mysql_query($sql) or die($sql);
		
		if(mysql_num_rows($res)){
			$row = mysql_fetch_row($res);
			$this->intSpecialFloorId = $row[0];
		} 
	}
	
	public function getSpecialRoomIdList()
	{
		//echo "getSpecialRoomIdList<br>";
		$this->getSpecialFloorId();
		$sql = " select room_id from rooms where floor_id=".$this->intSpecialFloorId;
		
		$res = mysql_query($sql) or die($sql);
		while(list($id)=mysql_fetch_row($res)) {
			$this->arrSpecialRooms[]=$id;
		}
		
	}
	
	public function getSpecialDetail()
	{
		$this->getSpecialRoomidList();
		$headers=array('Room Type','Food','Beer','Misc','Adjust','Deduct','Discount','Total');
		$retval.='<table class="report">';
		$retval.='<tr>';
		foreach($headers as $header){
			$retval.="<th>$header</th>";
		}
		$retval.='</tr>';
		
		foreach($this->arrSpecialRooms as $roomid) {
			$occupancy = $this->getOccupancy($roomid);
			if($occupancy) $retval.=$this->getSpecialDetailPerOccupancy($occupancy);
		}
		$retval.='<tr class="aggregates">';
		$retval.='<th>Totals: </th>';
		foreach($this->totals['specials'] as $total) {
			$retval.="<th>$total</th>";
		}
		$retval.='</tr>';
		$retval.='</table>';

		return $retval;
	}
	
	public function getSpecialFoodSalesByOccupancy($occupancy, $start, $end,$in=array(),$out=array()) 
	{
		if(count($in)) {
			$arrIn = implode(',',$in);
			$whereIn = " and category_id in ($arrIn) ";
		}
		if(count($out)) {
			$arrOut = implode(',',$out);
			$whereIn = " and category_id not in ($arrOut) ";
		}
		
		if($start !='') $add=" and update_date >= '$start' "; 
		if($end !='') $add.=" and update_date <= '$end' ";
		
		$_sql = "select sum(unit_cost*qty) from fnb_sales 
				where status in ('Paid')
				$whereIn $whereOut
				$add
				and occupancy_id = '$occupancy'";
		//echo "$occupancy $_sql<hr>";
		
		$_res = mysql_query($_sql) or die($sql);
		list($value) = mysql_fetch_row($_res);
		return $value;
	
	}
	
	public function getSpecialDetailPerOccupancy($occupancy)
	{
		$arrRoom = $this->getRoomDetailsByOccupancy($occupancy);
		$food = $this->getSpecialFoodSalesByOccupancy($occupancy, $this->start, $this->end, array(),array(17,21,22), 1,0,0,0 );
		$beer = $this->getSpecialFoodSalesByOccupancy($occupancy, $this->start, $this->end, array(17,21),array(), 1 ,0,0,0);
		$misc = $this->getRoomSalesByOccupancy($occupancy, $this->start, $this->end, array(),array(2,3), 'category_id', 1,0,0,0 );
		$adjust = $this->getRoomSalesByOccupancy($occupancy, $this->start, $this->end, array(18),array(), 'item_id',1,0,0,0 );
		$deduct = $this->getRoomSalesByOccupancy($occupancy, $this->start, $this->end, array(27),array(), 'item_id',1,0,0,0 );
		$discount = $this->getSpecialFoodSalesByOccupancy($occupancy, $this->start, $this->end, array(22),array(), 'item_id',1,0,0,0 );
		$total = $food + $beer + $misc + $adjust + $deduct + $discount;
		$this->totals['specials']['food']+=$food;
		$this->totals['specials']['beer']+=$beer;
		$this->totals['specials']['misc']+=$misc;
		$this->totals['specials']['adjust']+=$adjust;
		$this->totals['specials']['deduct']+=$deduct;
		$this->totals['specials']['discount']+=$discount;
		$this->totals['specials']['total']+=$total;
		$retval = '<tr>';
		$retval.='<td>'. $arrRoom['door_name'] .'</td>';
		$retval.='<td>'. $food .'</td>';
		$retval.='<td>'. $beer .'</td>';
		$retval.='<td>'. $misc .'</td>';
		$retval.='<td>'. $adjust .'</td>';
		$retval.='<td>'. $deduct .'</td>';
		$retval.='<td>'. $discount .'</td>';
		$retval.='<td>'. $total .'</td>';
		$retval.='<td class="debug"><a href="occupancydetails.php?occ='.$occupancy.'" target="_blank">'. $occupancy .'</a></td>';
		$retval.= '</tr>';
		
		return $retval;
	}
	
	public function getOccupancy($roomid) 
	{
		$sql  =" select occupancy_id from occupancy where room_id=$roomid order by occupancy_id desc ";
		$res = mysql_query($sql) or die($sql);
		if(mysql_num_rows($res)) {
			$row = mysql_fetch_array($res);
			$this->occupancydetails=$row;
			return $row[0];
		}
		return 0;
	}
	
	public function getRoomDetailsByOccupancy($occupancy) 
	{
		$sql  =" select rooms.* from occupancy, rooms where occupancy.room_id=rooms.room_id and occupancy.occupancy_id='$occupancy'";
		$res = mysql_query($sql) or die($sql);
		$row = mysql_fetch_array($res);
		return $row;
	}
	
	public function getPreviousShiftEnd($rid,$occupancy)
	{
		$sql = "select a.datetime 
				from `shift-transactions` a, room_sales b
				where a.shift = 'end' 
				and a.datetime >= b.sales_date 
				and b.roomsales_id=$rid
				order by datetime asc limit 0,1
			";
		$res = mysql_query($sql);
		if(mysql_num_rows($res)) {
			$row = mysql_fetch_row($res);
			return $row[0];
		}else{
			return 0;
		}
	}
	
	public function isFirst($rid,$occupancy) 
	{
		$sql  ="select count(*) from room_sales where roomsales_id <='$rid' and occupancy_id='$occupancy' ";
		$res  = mysql_query($sql) or die($sql);
		list($count) = mysql_fetch_row($res);
		return ($count == 1);
	}
	
	public function getlastrechitcutoffdate($rid,$occ)
	{
		$sql = "select `datetime` from `shift-transactions` a, room_sales b
			where b.roomsales_id='$rid' and b.occupancy_id='$occ' and
			b.sales_date < a.datetime and a.shift='start' limit 0,1
		";
		//if ($occ==6097) echo $sql;
		$res = mysql_query($sql) or die($sql);
		if(mysql_num_rows($res)) {
			$row = mysql_fetch_row($res);
			return $row[0];
		}else{
			return 0;
		}
	}
	
	public function getFoodSalesByOccupancy($occupancy, $start='', $end='', $in=array(),$out=array(),$site=1,$los=0, $days=0,$duration=0,$ratename='',$shift=1,$checkout=1,$amount=0,$roomtype=0) 
	{
		if(count($in)) {
			$arrIn = implode(',',$in);
			$whereIn = " and category_id in ($arrIn) ";
		}
		if(count($out)) {
			$arrOut = implode(',',$out);
			$whereIn = " and category_id not in ($arrOut) ";
		}
		if($site==2) {
			$lastshiftend = $this->getPreviousShiftEnd($this->selectRoomsaleId[$occupancy],$occupancy);
			if($checkout) {
				$arrRSID = explode(',', $this->multipleRoomSaleIds[$occupancy]);
				if(count($arrRSID) > 1) {
					sort($arrRSID);
					$roomsaleid=$arrRSID[0];
				}else{
					$roomsaleid=$this->selectRoomsaleId[$occupancy];
				}
				if($los > 1) {
					//$roomsaleid=$this->selectRoomsaleId[$occupancy];
					if($this->isFirst($roomsaleid, $occupancy)) {
						$daterange=" and update_date >='' and update_date <='$end' ";
					}else{
						//get the last rechit date and start from there
						$newstart = $this->getlastrechitcutoffdate($roomsaleid,$occupancy);
						//$newstart = date('Y-m-d H:i:s',strtotime("$end - $duration hours"));
						$daterange=" and update_date >='$newstart' and update_date <='$end' ";
					}
				}else{
					//$roomsaleid=$this->selectRoomsaleId[$occupancy];
					if($this->isFirst($roomsaleid, $occupancy)) {
						$daterange=" and update_date >='' and update_date <='$end' ";
					}else{
						$newstart = date('Y-m-d H:i:s',strtotime("$start - $duration hours"));
						$daterange=" and update_date >='$newstart' and update_date <='$end' ";
					}
				}	
			}else{
				$roomsaleid=$this->selectRoomsaleId[$occupancy];
				if($days > 0) {
					if($this->numDays[$occupancy] <= 1) {
						$newstart = date('Y-m-d H:i:s',strtotime("$end - $duration hours"));
						$daterange = " and update_date >=''
						and update_date <='$end' ";
					} else {
						$newstart = date('Y-m-d H:i:s',strtotime("$end - $duration hours"));
						$newstart = $this->getlastrechitcutoffdate($roomsaleid,$occupancy);
						$daterange = " and update_date >='$newstart'
						and update_date <='$end' ";
					}
				}else{
					if ($los > 1) {
						$newstart = date('Y-m-d H:i:s',strtotime("$end - $duration hours"));
						$newstart = $this->getlastrechitcutoffdate($roomsaleid,$occupancy);
						$daterange = " and update_date >='$newstart'
							and update_date <='$end' ";
					}else{
						$newstart = date('Y-m-d H:i:s',strtotime("$start - $duration hours"));
						$daterange = " and update_date >='$newstart'
							and update_date <='$end' ";
					}
				}
			}
		}elseif($site==1){
			if($start !='') $add=" and sales_date >= '$start' "; 
		    if($end !='') $add.=" and sales_date <= '$end' ";
		}
		$_sql = "select sum(unit_cost*qty) from fnb_sales 
				where status in ('Paid')
				$whereIn $whereOut
				$daterange
				$add
				and occupancy_id = '$occupancy'";
		
		//if($site==2 && $occupancy==4002) echo "L$los C$checkout D$duration $occupancy $days $_sql<hr>";
		
		$_res = mysql_query($_sql) or die($sql);
		list($value) = mysql_fetch_row($_res);
		return $value;
	}


	
	public function getMultipleRoomsales($rid, $occupancy, $start, $end)
	{
		$sql = " select roomsales_id,sales_date from room_sales where 
				item_id=15
				and status='Paid'
				and occupancy_id='$occupancy'
				and roomsales_id<>'$rid'
				 ";
		$res = mysql_query($sql) or die(mysql_error() . $sql);
		$ret = $rid;

		while(list($newrid,$date)=mysql_fetch_row($res)) {
			$sql3 = "select d.rate_id, e.duration, a.unit_cost, b.room_id, b.room_type_id 
				from room_sales a, rooms b, occupancy c, room_type_rates d, rates e
				where 
				a.unit_cost=d.amount
				and b.room_type_id=d.room_type_id
				and a.occupancy_id=c.occupancy_id
				and c.room_id=b.room_id
				and d.rate_id=e.rate_id
				and a.roomsales_id='$newrid'
				";
			//if ($occupancy==6097) echo "$occupancy C$checkout $sql <br> $sql3 <hr>";
			$res3 = mysql_query($sql3) or die($sql3);
			list($rateid, $duration) = mysql_fetch_row($res3);
			/*
			if($rateid==1) {
				$duration=3;
			}elseif($rateid==2){
				$duration=12;
			}elseif($rateid==3){
				$duration=24;
			}
			*/
			$newdate = date("Y-m-d H:i:s", strtotime("$date +$duration hours"));
			$sql2 = " select datetime from `shift-transactions`  
				where datetime > '$newdate'
				and shift='end' order by datetime limit 0,1 ";
			$res2 = mysql_query($sql2) or die(mysql_error() . $sql2);
			list($enddate) = mysql_fetch_row($res2);
			//if ($occupancy==6097) echo "$rid <><> $newrid SD: $date   END:$end --- $enddate<br>";
			if($enddate==$end) {
				$ret .= ",$newrid";
			}
		}
		$this->multipleRoomSaleIds[$occupancy]=$ret;
		return $ret;
	}
	
	public function getCurrentRateIdByOccupancy($occupancy, $start='', $end='',$checkout=0) 
	{
		if($checkout==1) {
			$sql = "select a.roomsales_id, a.sales_date 
				from room_sales a 
				where a.occupancy_id=$occupancy
				and a.item_id=15
				order by a.sales_date desc limit 1
				";
			
		}elseif($checkout==0){
			$sql = "select a.roomsales_id, a.sales_date 
					from room_sales a 
					where a.occupancy_id=$occupancy
					and a.item_id=15
					and a.sales_date <='$start' 
					order by a.sales_date desc limit 1
					";
			
		}
		
		$res = mysql_query($sql) or die(mysql_error().$sql);
		list($salesid, $thischeckin) = mysql_fetch_row($res);
		$thisid=$salesid;
		$this->getNumDaysOfStay($thisid,$occupancy);
		$this->selectRoomsaleId[$occupancy]=$thisid;
		//if($occupancy==5674) echo $thisid.'<hr>'.$occupancy.$sql;
		$sql3 = "select d.rate_id, a.unit_cost, a.room_id, b.room_type_id 
				from room_sales a, rooms b, occupancy c, room_type_rates d
				where 
				a.unit_cost=d.amount
				and b.room_type_id=d.room_type_id
				and a.occupancy_id=c.occupancy_id
				and c.room_id=b.room_id
				and a.roomsales_id='$thisid'
				";
		//if ($occupancy==5674) echo "$occupancy C$checkout $sql <br> $sql3 <hr>";
		$res3 = mysql_query($sql3) or die();
		$row3 = mysql_fetch_row($res3);
		
		return $row3[0];
	}
	
	public function getNumDaysOfStay($rid, $occ)
	{
		$sql = " select * from room_sales where occupancy_id=$occ and roomsales_id <='$rid' ";
		$res = mysql_query($sql) or die($sql);
		$num = mysql_num_rows($res);
		$this->numDays[$occ]=$num;
	}
	
	public function getRoomRateByOccupancy($occupancy, $start='', $end='' ,$site=1,$los=0, $days=0,$duration=0,$shiftnum=1,$checkout)
	{
		//set options later for rechit here
		if ($site==1) {
			$sql = "select unit_cost*qty from room_sales
					where occupancy_id=$occupancy
					and item_id=15
					and status='Paid'
					order by sales_date desc limit 1
				   ";
		} elseif($site==2) {
 		    $roomsaleid=$this->selectRoomsaleId[$occupancy];

			if($this->isFirst($roomsaleid, $occupancy)) {
				$sql = "select (unit_cost*qty) from room_sales where  status in ('Paid','Draft') and roomsales_id=$roomsaleid";
			}else{
				$roomslist = $this->getMultipleRoomsales($roomsaleid, $occupancy, $start, $end);
				$sql = "select sum(unit_cost*qty) from room_sales
					   where  status in ('Paid','Draft') and roomsales_id in ($roomslist) ";
			}
		}
		//if($site==2 && $occupancy==6097) echo "$rowCount $roomsaleid $occupancy los=$los days=$days checkount=$checkout= $duration $site $sql<hr>";
		$res = mysql_query($sql) or die($sql);
		$row = mysql_fetch_row($res);
		return $row[0];
	}

	
	public function getRoomDiscountByOccupancy($occupancy, $start='', $end='' ,$site=1,$los=0, $days=0,$duration=0,$shiftnum=1,$checkout)
	{
		//set options later for rechit here
		if ($site==1) {
			$sql = "select unit_cost*qty from room_sales
					where occupancy_id=$occupancy
					and item_id=17
					and status in ('Paid','Draft')
					order by sales_date desc limit 1
				   ";
		} elseif($site==2) {
			$newstart = date('Y-m-d H:i:s',strtotime("$start - $duration hours"));
			if($days > 0) {
				if($checkout) {
					$daterange = " and sales_date >='$newstart'
					and sales_date <='$end' ";
				}else{
					$newstart = date('Y-m-d H:i:s',strtotime("$start - $duration hours"));
					$newend = date('Y-m-d H:i:s',strtotime("$start"));
					$daterange = " and sales_date >='$newstart'
					and sales_date <='$newend' ";
				}
			}else{
				if ($los > 1) {
					$daterange = " and sales_date >='$newstart'
						and sales_date <='$end' ";
				}
			}
			$sql = "select unit_cost*qty from room_sales
					where occupancy_id=$occupancy
					and item_id=17
					and status in ('Paid','Draft')
					$daterange
					order by sales_date desc 
				   ";
		}
		//echo "$occupancy $los $days $checkout $duration $site $sql<hr>";
		$res = mysql_query($sql);
		$row = mysql_fetch_row($res);
		return $row[0];
	}
	
	public function getRoomDeductByOccupancy($occupancy, $start='', $end='' ,$site=1,$los=0, $days=0,$duration=0,$shiftnum=1,$checkout)
	{
		//set options later for rechit here
		if ($site==1) {
			$sql = "select unit_cost*qty from room_sales
					where occupancy_id=$occupancy
					and item_id=27
					and status in ('Paid','Draft')
					order by sales_date desc limit 1
				   ";
		} elseif($site==2) {
			$newstart = date('Y-m-d H:i:s',strtotime("$start - $duration hours"));
			if($days > 0) {
				if($checkout) {
					$daterange = " and sales_date >='$newstart'
					and sales_date <='$end' ";
				}else{
					$newstart = date('Y-m-d H:i:s',strtotime("$start - $duration hours"));
					$newend = date('Y-m-d H:i:s',strtotime("$start"));
					$daterange = " and sales_date >='$newstart'
					and sales_date <='$newend' ";
				}
			}else{
				if ($los > 1) {
					$daterange = " and sales_date >='$newstart'
						and sales_date <='$end' ";
				}
			}
			$sql = "select unit_cost*qty from room_sales
					where occupancy_id=$occupancy
					and item_id=27
					and status in ('Paid','Draft')
					$daterange
					order by sales_date desc 
				   ";
		}
		//echo "$occupancy $los $days $checkout $duration $site $sql<hr>";
		$res = mysql_query($sql);
		$row = mysql_fetch_row($res);
		return $row[0];
	}

	public function getRoomAdjustByOccupancy($occupancy, $start='', $end='' ,$site=1,$los=0, $days=0,$duration=0,$shiftnum=1,$checkout)
	{
		//set options later for rechit here
		if ($site==1) {
			$sql = "select sum(unit_cost*qty) from room_sales
					where occupancy_id=$occupancy
					and item_id=18
					and status in ('Paid','Draft')
					order by sales_date desc limit 1
				   ";
		} elseif($site==2) {
			$newstart = date('Y-m-d H:i:s',strtotime("$start - $duration hours"));
			if($days > 0) {
				if($checkout) {
					$daterange = " and sales_date >='$newstart'
					and sales_date <='$end' ";
				}else{
					$newstart = date('Y-m-d H:i:s',strtotime("$start - $duration hours"));
					$newend = date('Y-m-d H:i:s',strtotime("$start"));
					$daterange = " and sales_date >='$newstart'
					and sales_date <='$newend' ";
				}
			}else{
				if ($los > 1) {
					$daterange = " and sales_date >='$newstart'
						and sales_date <='$end' ";
				}
			}
			$sql = "select sum(unit_cost*qty) from room_sales
					where occupancy_id=$occupancy
					and item_id=18
					and status in ('Paid','Draft')
					$daterange
					order by sales_date desc 
				   ";
		}
		//echo "$occupancy $los $days $checkout $duration $site $sql<hr>";
		$res = mysql_query($sql);
		$row = mysql_fetch_row($res);
		return $row[0];
	}

	public function getRoomCheckinExtensionByOccupancy($occupancy, $start='', $end='' ,$site=1,$los=0, $days=0,$duration=0,$shiftnum=1,$checkout)
	{
		if ($site==1) {
			$sql = "select a.unit_cost*a.qty, a.roomsales_id
				FROM room_sales a, occupancy_log b
				WHERE a.occupancy_id = b.occupancy_id
				And a.sales_date=b.transaction_date
				AND a.item_id = '16'
				AND b.remarks not in ('Overtime on checkout')
				AND a.status in ('Paid')
				AND a.occupancy_id = '$occupancy'";
		} elseif($site==2) {
			$newstart = date('Y-m-d H:i:s',strtotime("$start - $duration hours"));
			if($days > 0) {
				if($checkout==1) {
					//echo "here";
					$daterange = " and update_date >='$start'
					and update_date <='$end' ";
				}else{
					//echo "dito";
					$newstart = date('Y-m-d H:i:s',strtotime("$start - $duration hours"));
					$daterange = " and sales_date >='$newstart'
					and sales_date <='$start' ";
				}
			}else{
				//echo "eto pala";
				if($checkin==1) {
					$daterange="";
				}elseif ($los > 1) {
					$daterange = " and sales_date >='$newstart'
						and sales_date <='$end' ";
				}
				
			}
			$sql = "select a.unit_cost*a.qty, a.roomsales_id
				FROM room_sales a, occupancy_log b
				WHERE a.occupancy_id = b.occupancy_id
				And a.sales_date=b.transaction_date
				AND a.item_id = '16'
				AND b.remarks not in ('Overtime on checkout')
				AND a.status  in ('Paid','Draft')
				$daterange
				AND a.occupancy_id = '$occupancy' ";
					
			$sql = "select a.unit_cost*a.qty, a.roomsales_id
				FROM room_sales a, occupancy_log b
				WHERE a.occupancy_id = b.occupancy_id
				And (a.sales_date=b.transaction_date or a.update_date=b.transaction_date)
				AND a.item_id = '16'
				AND b.remarks not in ('Overtime on checkout')
				AND a.status  in ('Paid','Draft')
				$daterange
				AND a.occupancy_id = '$occupancy' ";
			
			/*
			$sql = "select a.unit_cost*a.qty, a.roomsales_id
				FROM room_sales a, occupancy_log b
				WHERE a.occupancy_id = b.occupancy_id
				AND a.item_id = '16'
				AND b.remarks not in ('Overtime on checkout')
				AND a.status in ('Paid')
				$daterange
				AND a.occupancy_id = '$occupancy' ";
			*/
		}
		//if($occupancy==6097) echo "$occupancy $days $los $checkout $sql<hr>";
		$res = mysql_query($sql);
		$row = mysql_fetch_row($res);
		
		return $row[0];
	}
	
	public function getRoomCheckoutOTByOccupancy($occupancy, $start='', $end='' ,$site=1,$los=0, $days=0,$duration=0,$shiftnum=1)
	{
		if($site==2) {
			$newstart = date('Y-m-d H:i:s',strtotime("$start - $duration hours"));
			if($days > 0) {
				$daterange = " and a.sales_date >='$start'
					and a.sales_date <='$end' ";
				
			}else{
				if ($los > 1) {
					$daterange = " and a.sales_date >='$start'
						and a.sales_date <='$end' ";
				}
			}
		}
		$sql = "select a.roomsales_id
			FROM room_sales a, occupancy_log b
			WHERE a.occupancy_id = b.occupancy_id
			And a.sales_date=b.transaction_date
			AND a.item_id = '16'
			AND b.remarks not in ('Overtime on checkout')
			AND a.status in ('Paid')
			$daterange
			AND a.occupancy_id = '$occupancy'";
		$res = mysql_query($sql);

		if(mysql_num_rows($res)) {
			list($inextid) = mysql_fetch_row($res);
		}
		
		$sql = "select sum(a.unit_cost*a.qty)
			FROM room_sales a, occupancy_log b
			WHERE a.occupancy_id = b.occupancy_id
			AND a.item_id = '16'
			AND b.remarks in ('Overtime on checkout')
			and a.roomsales_id not in ('$inextid')
			$daterange
			AND a.occupancy_id = '$occupancy'";
		//if($occupancy==4264) echo "$occupancy $site $days $los $duration shiftnum $sql<hr> ";
		$res = mysql_query($sql);
		$row = mysql_fetch_row($res);
		return $row[0];
	}
	
	public function getRoomSalesByOccupancy($occupancy, $start, $end, $in=array(),$out=array(),$key='category_id',$site=1,$los=0, $days=0,$duration=0,$shiftnum=1,$checkout=1)
	{
		//echo "$occupancy $los, $days, $duration, $site<br />";
		if(count($in)) {
			$arrIn = implode(',',$in);
			$whereIn = " and $key in ($arrIn) ";
		}
		if(count($out)) {
			$arrOut = implode(',',$out);
			$whereIn = " and $key not in ($arrOut) ";
		}
		
		if($site==2) {
			if($checkout != 0) {
				$arrRSID = explode(',', $this->multipleRoomSaleIds[$occupancy]);
				if(count($arrRSID) > 1) {
					sort($arrRSID);
					$roomsaleid=$arrRSID[0];
				}else{
					$roomsaleid=$this->selectRoomsaleId[$occupancy];
				}
				if($los > 1) {	
					//$roomsaleid=$this->selectRoomsaleId[$occupancy];
					if($this->isFirst($roomsaleid, $occupancy)) {
						$daterange=" and update_date >='' and update_date <='$end' ";
					}else{
						//get the last rechit date and start from there
						$newstart = $this->getlastrechitcutoffdate($roomsaleid,$occupancy);
						//$newstart = date('Y-m-d H:i:s',strtotime("$end - $duration hours"));
						$daterange=" and update_date >='$newstart' and update_date <='$end' ";
					}
				}else{
					//$roomsaleid=$this->selectRoomsaleId[$occupancy];
					if($this->isFirst($roomsaleid, $occupancy)) {
						$daterange=" and update_date >='' and update_date <='$end' ";
					}else{
						$newstart = date('Y-m-d H:i:s',strtotime("$start - $duration hours"));
						$daterange=" and update_date >='$newstart' and update_date <='$end' ";
					}
				}
			}elseif($checkout==0){
				$roomsaleid=$this->selectRoomsaleId[$occupancy];
				if($this->isFirst($roomsaleid,$occupancy)) {
					$newstart = '';
				}else{
					$newstart = $this->getlastrechitcutoffdate($roomsaleid,$occupancy);
				}
				if($days > 0) {
					if($los > 1) {
						//$newstart = date('Y-m-d H:i:s',strtotime("$start - $duration hours"));
						//$newstart = $this->getlastrechitcutoffdate($roomsaleid,$occupancy);
						$daterange = " and update_date >='$newstart'
						and update_date <='$end' ";
					}else{
						$newstart = date('Y-m-d H:i:s',strtotime("$end - $duration hours"));
						$daterange = " and update_date >=''
						and update_date <='$end' ";
					}
					
				}else{
					if ($los > 1) {
						//$newstart = date('Y-m-d H:i:s',strtotime("$end - $duration hours"));
						$daterange = " and update_date >='$newstart'
							and update_date <='$end' ";
					}else{
						//$newstart = date('Y-m-d H:i:s',strtotime("$start - $duration hours"));
						//$newstart = $this->getlastrechitcutoffdate($roomsaleid,$occupancy);
						$daterange = " and update_date >='$newstart'
							and update_date <='$end' ";
					}
				}
			}
		}elseif($site==1){
			if($start !='') $add=" and sales_date >= '$start' "; 
			if($end !='') $add.=" and sales_date <= '$end' ";
		}
		$_sql = "select sum(unit_cost*qty) from room_sales 
				where status in ('Paid')
				$whereIn $whereOut  $add
				$daterange
				and occupancy_id = '$occupancy' ";
		
		$_res = mysql_query($_sql) or die($_sql);
	
		//if($occupancy==6097) echo "$occupancy S$site L$los D$days C$checkout $duration $_sql $roomsaleid<hr>";
		
		list($value) = mysql_fetch_row($_res);
		//if($arrIn=='17') echo $_sql."<hr>";
		return $value;
	}	
	
	public function getWalkupDetail()
	{
		$exceptList = implode(',',$this->arrSpecialRooms);
		$sql = "select * from occupancy a, rooms b, room_types c, rates d 
				where a.actual_checkout >= '$this->start' 
				and a.actual_checkout <= '$this->end' 
				and a.room_id = b.room_id
				and b.room_type_id=c.room_type_id
				and a.rate_id=d.rate_id
				and b.site_id=1
				and b.room_id not in ($exceptList)
				order by b.room_type_id asc,
				a.rate_id desc,
				a.actual_checkout asc";
		$res = mysql_query($sql) or die($sql);			

		while( $row=mysql_fetch_array($res) ) {
			$retval.=$this->getWalkinDetailPerOccupancy($row);
		}
		
		if(isset($this->totals['walkin'])) {
			$retval.='<tr class="aggregates">';
			$retval.='<th>Totals: </th>';
			foreach($this->totals['walkin'] as $total) {
				if(is_numeric($total)) $total = number_format($total,2);
				$retval.="<th>$total</th>";
			}
			$retval.='</tr>';
		}
		
		
		return $retval;
	}
	
	public function getRates()
	{
		$sql = " select rate_id, duration from rates ";
		$res = mysql_query($sql) or die($sql);
		while($row = mysql_fetch_row($res)) {
			$this->rates[$row[0]]=$row[1];
		}
	}
	
	public function getWalkinDetailPerOccupancy($row)
	{
		$occupancy = $row['occupancy_id'];
		$doorname = $row['door_name'];
		$ratename = $row['duration'];
		$roomtypename = $row['room_type_name'];
		
		$food = $this->getFoodSalesByOccupancy($occupancy, '', '', array(),array(17,21) );
		$beer = $this->getFoodSalesByOccupancy($occupancy, '', '', array(17,21),array() );
		$misc = $this->getRoomSalesByOccupancy($occupancy, '', '', array(),array(2,3) );
		$adjust = $this->getRoomSalesByOccupancy($occupancy, '', '', array(18),array(),'item_id' );
		$deduct = $this->getRoomSalesByOccupancy($occupancy, '', '', array(27),array(),'item_id' );
		$discount = $this->getRoomSalesByOccupancy($occupancy, '', '', array(17),array(),'item_id' );
		$roomrate = $this->getRoomRateByOccupancy($occupancy);
		$in_ot = $this->getRoomCheckinExtensionByOccupancy($occupancy);
		$out_ot = $this->getRoomCheckoutOTByOccupancy($occupancy);
		
		$in_total=$in_ot + $roomrate + $out_ot + $discount + $deduct ;
		
		$total = $food + $beer + $misc + $in_total + $adjust;
		
		$this->totals['walkin']['doorname']+=1;
		$this->totals['walkin']['ratename']+=1;
		$this->totals['walkin']['roomrate']+=$roomrate;
		$this->totals['walkin']['ext']+=$in_ot;
		$this->totals['walkin']['discount']+=$discount;
		$this->totals['walkin']['in_total']+=$in_total;
		$this->totals['walkin']['chkin']='&nbsp;';
		$this->totals['walkin']['chkout']='&nbsp;';
		$this->totals['walkin']['food']+=$food;
		$this->totals['walkin']['beer']+=$beer;
		$this->totals['walkin']['misc']+=$misc;
		$this->totals['walkin']['adjust']+=$adjust;
		$this->totals['walkin']['deduct']+=$deduct;
		$this->totals['walkin']['out_ot']+=$out_ot;
		$this->totals['walkin']['total']+=$total;
		$retval = '<tr>';
		$retval.='<td>'. $roomtypename .'</td>';
		$retval.='<td>'. $doorname .'</td>';
		$retval.='<td>'. $ratename .'</td>';
		$retval.='<td>'. $roomrate .'</td>';
		$retval.='<td>'. $in_ot .'</td>';
		$retval.='<td>'. $discount .'</td>';
		$retval.='<td>'. $in_total .'</td>';
		$retval.='<td nowrap>'. date('m/d H:i',strtotime($row['actual_checkin'])) .'</td>';
		$retval.='<td nowrap>'. date('m/d H:i',strtotime($row['actual_checkout'])) .'</td>';
		$retval.='<td>&nbsp;'. $food .'</td>';
		$retval.='<td>&nbsp;'. $beer .'</td>';
		$retval.='<td>&nbsp;'. $misc .'</td>';
		$retval.='<td>&nbsp;'. $adjust .'</td>';
		$retval.='<td>&nbsp;'. $deduct .'</td>';
		$retval.='<td>&nbsp;'. $out_ot .'</td>';
		$retval.='<td>&nbsp;'. $total .'</td>';
		$retval.='<td class="debug"><a href="occupancydetails.php?occ='.$row['occupancy_id'].'" target="_blank">'. $row['occupancy_id'] .'</a></td>';
		$retval.= '</tr>';
		return $retval;
	}
	
	public function getHotelDetail()
	{
		$exceptList = implode(',',$this->arrSpecialRooms);
		$cutoff = date('Y-m-d H:i:s', strtotime($this->start . ' -24 hours'));
		$lastshift=$this->shiftnum - 1 ;
		$startshift = $this->start;
		$endshift = $this->end;
		$sql = "drop table if exists temphoteldetails;";
		
		mysql_query($sql) or die($sql);
		
				
		$sql = "
				create table temphoteldetails
				select distinct a.occupancy_id,a.actual_checkin,a.actual_checkout,
				b.site_id,b.door_name,a.room_id,c.room_type_id, c.room_type_name,c.rank
				,d.rate_id,d.rate_name,d.duration, a.shift_checkin,
				if(actual_checkout='0000-00-00 00:00:00',timestampdiff(HOUR,a.actual_checkin,now())/d.duration,
				if(actual_checkout > '$this->end',timestampdiff(HOUR,a.actual_checkin,'$this->end')/d.duration, '0')) as 'status',
				if(actual_checkout='0000-00-00 00:00:00',timestampdiff(HOUR,a.actual_checkin,now())/d.duration,
				if(actual_checkout > '$this->end',timestampdiff(HOUR,a.actual_checkin,'$this->end')/d.duration, 
				timestampdiff(HOUR,a.actual_checkin,a.actual_checkout)/d.duration)) as 'los',
				if(a.actual_checkout >= '$this->start' and a.actual_checkout <= '$this->end',1,0) as 'checkout'
				from occupancy a, rooms b, room_types c, rates d , room_sales e
				where 
				(
				(a.actual_checkout >= '$this->start' and a.actual_checkout <= '$this->end' )
				or 
				(e.sales_date >= '$this->start' and e.sales_date <= '$this->end' and a.actual_checkin <> e.sales_date and e.item_id=15)
				)
				and a.occupancy_id=e.occupancy_id
				and a.room_id = b.room_id
				and b.room_type_id=c.room_type_id
				and a.rate_id=d.rate_id
				and b.site_id=2
				and a.room_id not in ($exceptList)
				";	

		mysql_query($sql) or die(mysql_error() . $sql);		

		$sql = " select * from temphoteldetails
				order by rank asc, room_type_id asc,
				rate_id desc,
				actual_checkout asc";
	
		$res = mysql_query($sql) or die(mysql_error() . $sql);			

		while( $row=mysql_fetch_array($res) ) {
			$retval.=$this->getHotelDetailPerOccupancy($row,$startshift,$endshift);
		}
		if(isset($this->totals['hotel'])) {
			$retval.='<tr class="aggregates">';
			$retval.='<th>Totals: </th>';
			foreach($this->totals['hotel'] as $total) {
				if($total==0) $total='&nbsp;';
				$retval.="<th>$total</th>";
			}
			$retval.='</tr>';
		}
		
		return $retval;
	}
	
	public function getDurationFromRate($amount,$type)
	{
		$sql = "select rate_id from room_type_rates where amount='$amount' and room_type_id='$type' and active=1";
		$res = mysql_query($sql);
		$row = mysql_fetch_row($res);
		return $row[0];
	}
	
	
	public function getHotelDetailPerOccupancy($row,$start,$end)
	{
		/*
		* summaries
		$this->summary['roomtypename']['occupancy_total']=
			$this->summary['roomtypename']['3hrs'] + 
			$this->summary['roomtypename']['12hrs'] + 
			$this->summary['roomtypename']['24hrs']
		$this->summary['roomtypename']['sales_total'] =
			$this->summary['roomtypename']['Room']
			$this->summary['roomtypename']['Ot']
			$this->summary['roomtypename']['Food']
			$this->summary['roomtypename']['beer']
			$this->summary['roomtypename']['misc']
			$this->summary['roomtypename']['adj']
			$this->summary['roomtypename']['deduct']
		
		*/
		$occupancy = $row['occupancy_id'];
		$doorname = $row['door_name'];
		//$ratename = $row['duration'];
		
		
		$rateId = $this->getCurrentRateIdByOccupancy($occupancy, $start, $end, $row['checkout']);
		
		$currentRateName = $ratename = $duration = $this->rates[$rateId];
		//if ($occupancy==5674) var_dump($rateId);
		/*
		if($rateId==1) {
			$ratename = 3; 
			$currentRateName = '3 HRS';
		}elseif($rateId==2) {
			$ratename = 12;
			$currentRateName = '12 HRS';
		}if($rateId==3) {
			$ratename = 24; 
			$currentRateName = '24 HRS';
		}
		*/
		
		$roomtypename = $row['room_type_name'];
		$room_type_id = $row['room_type_id'];
		
		//if($occupancy==2329) echo "<pre>".print_r($row)."</pre>";
		//echo "$occupancy $doorname $rateId $ratename $currentRateName $roomtypename<br>";
		/*
		$food =(int)  $this->getFoodSalesByOccupancy($occupancy, $this->start, $this->end, array(),array(17,21),$row['site_id'],ceil($row['los']),floor($row['status']), $ratename );
		$beer = $this->getFoodSalesByOccupancy($occupancy, $this->start, $this->end, array(17,21),array(),$row['site_id'],ceil($row['los']),floor($row['status']), $ratename );
		
		*/
//echo "roomrate $occupancy";
		$roomrate =(int)  $this->getRoomRateByOccupancy($occupancy, $this->start, $this->end,$row['site_id'],ceil($row['los']),floor($row['status']),$ratename,$row['shift_checkin'],$row['checkout']);
//echo "food $occupancy";	
		$food = (int) $this->getFoodSalesByOccupancy($occupancy, $this->start, $this->end, array(),array(17,21),$row['site_id'],ceil($row['los']),floor($row['status']), $ratename,$row['shiftcheckin'], $row['checkout'],$row['checkout'],$roomrate,$room_type_id );
//echo "beer $occupancy";		
		$beer = (int) $this->getFoodSalesByOccupancy($occupancy, $this->start, $this->end, array(17,21),array(),$row['site_id'],ceil($row['los']),floor($row['status']), $ratename,$row['shiftcheckin'], $row['checkout'],$row['checkout'],$roomrate,$room_type_id );
//echo "misc $occupancy";
		$misc =(int)  $this->getRoomSalesByOccupancy($occupancy, $this->start, $this->end, array(),array(2,3),'category_id',$row['site_id'],ceil($row['los']),floor($row['status']), $ratename,$row['shiftcheckin'], $row['checkout']  );
		//$adjust = $this->getRoomSalesByOccupancy($occupancy,$this->start, $this->end, array(18),array(),'item_id',$row['site_id'],ceil($row['los']),floor($row['status']), $ratename,$row['shiftcheckin'], $row['checkout']  );
//echo "adjust $occupancy";
		$adjust =(int)  $this->getRoomAdjustByOccupancy($occupancy, $this->start, $this->end,$row['site_id'],ceil($row['los']),floor($row['status']),$ratename,$row['shift_checkin'],$row['checkout']);
//echo "deduct $occupancy";
		//$deduct = $this->getRoomSalesByOccupancy($occupancy, $this->start, $this->end, array(27),array(),'item_id',$row['site_id'],ceil($row['los']),floor($row['status']), $ratename,$row['shiftcheckin'], $row['checkout']  );
		$deduct = (int) $this->getRoomDeductByOccupancy($occupancy, $this->start, $this->end,$row['site_id'],ceil($row['los']),floor($row['status']),$ratename,$row['shift_checkin'],$row['checkout']);
		$discount = (int) $this->getRoomDiscountByOccupancy($occupancy, $this->start, $this->end,$row['site_id'],ceil($row['los']),floor($row['status']),$ratename,$row['shift_checkin'],$row['checkout']);

		//$discount =(int)  $this->getRoomSalesByOccupancy($occupancy,$this->start, $this->end, array(17),array(),'item_id',$row['site_id'],ceil($row['los']),floor($row['status']), $ratename,$row['shiftcheckin'], $row['checkout']  );
		//$discount = $this->getRoomSalesByOccupancy($occupancy,$this->start, $this->end, array(17),array(),'item_id',$row['site_id'],ceil($row['los']),floor($row['status']), $ratename  );

		$in_ot =(int)  $this->getRoomCheckinExtensionByOccupancy($occupancy, $this->start, $this->end,$row['site_id'],ceil($row['los']),floor($row['status']),$ratename,$row['shift_checkin'],$row['checkout']);
		$out_ot =(int)  $this->getRoomCheckoutOTByOccupancy($occupancy, $this->start, $this->end,$row['site_id'],ceil($row['los']),floor($row['status']),$ratename,$row['shift_checkin'],$row['checkout']);
		
		$in_total=$in_ot + $roomrate  + $discount + $deduct + $adjust;
		if($in_total==0) {
			$this->cancel['hotel'][$roomtypename][$ratename] += 1;
		}
		
		//echo "$adjust + $in_ot + $roomrate  + $discount + $deduct + $out_ot = $in_total<hr>";
		$total = $food + $beer + $misc + $adjust + $in_ot + $roomrate  + $discount + $deduct + $out_ot ;
		
		//from *.109
		//$in_total= $roomrate  + $discount + $deduct + $adjust;<jammer> 2010-08-17, add ext to in_total c/o rbm	
		$total = $food + $beer + $misc + $in_ot + $roomrate  + $discount + $deduct + $out_ot + $adjust ;
		$total = $food + $beer + $misc + $in_ot + $roomrate + $out_ot + $discount + $deduct + $adjust;
		
		//$in_total=$in_ot + $roomrate + $out_ot + $discount + $deduct  + $adjust;
		//$total = $food + $beer + $misc + $in_ot + $roomrate + $out_ot + $discount + $deduct + $adjust;
		if($in_total > 0) {
			$this->totals['hotel']['doorname']+=1;
			$this->totals['hotel']['ratename']+=1;
		}
		$this->totals['hotel']['roomrate']+=$roomrate;
		$this->totals['hotel']['ext']+=$in_ot;
		$this->totals['hotel']['discount']+=$discount;
		$this->totals['hotel']['in_total']+=$in_total;
		$this->totals['hotel']['chkin']='&nbsp;';
		$this->totals['hotel']['chkout']='&nbsp;';
		$this->totals['hotel']['food']+=$food;
		$this->totals['hotel']['beer']+=$beer;
		$this->totals['hotel']['misc']+=$misc;
		$this->totals['hotel']['adjust']+=$adjust;
		$this->totals['hotel']['deduct']+=$deduct;
		$this->totals['hotel']['out_ot']+=$out_ot;
		$this->totals['hotel']['total']+=$total;

		
		//$this->summary[$roomtypename][$row['rate_name']]+=1;
		$this->summary[$roomtypename][$currentRateName]+=1;
		
		//$this->summary[$roomtypename]['room']+=$in_total;
		//$this->summary[$roomtypename]['ot']+= $out_ot;
		$this->summary[$roomtypename]['room']+=$roomrate + $discount + $adjust + $deduct;
		$this->summary[$roomtypename]['ot']+= $out_ot + $in_ot;
		
		$this->summary[$roomtypename]['food']+=$food;
		$this->summary[$roomtypename]['beer']+=$beer;
		$this->summary[$roomtypename]['misc']+=$misc;
		$this->summary[$roomtypename]['adjust']+=$adjust;
		$this->summary[$roomtypename]['deduct']+=$deduct;
		$this->summary[$roomtypename]['sales_total']+=$total;
		$this->grandtotal+=$total;

		//save details to tempdetails for summary report (per floor/roomtype)
		
		$hr = date('H', strtotime($start));
		if($hr == '23' && $this->shiftnum==1) {
			$salesdate = date('Y-m-d', strtotime($start . "+ 1 day"));
		}elseif($hr=='00' && $this->shiftnum==3) {
			$salesdate = date('Y-m-d', strtotime($start . "- 1 day"));
		}else{
			$salesdate = date('Y-m-d', strtotime($start));
		}
		$this->hotelsummary['shiftnum'] = $this->shiftnum;
		
		$detail_array = array(
			'occupancy' => $occupancy,
			'shiftnum'	=> $this->shiftnum,
			'salesdate' => $salesdate,
			'type'		=> 2,
			'allcounttotal' => 1,
			'roomtypename' => $roomtypename,
			'doorname'  => $doorname,
			'ratename'  => $ratename,
			'room'  	=> $roomrate + $discount + $adjust + $deduct,
			'ot'	    => $out_ot + $in_ot,
			'food'		=> $food,
			'beer'		=> $beer,
			'misc'		=> $misc,
			'total'		=> $total,
		);
		if ($start) {
			$this->saveToDetailSummary($detail_array);
		}
		if($roomrate==0) $roomrate='&nbsp;';
		if($in_ot==0) $in_ot='&nbsp;';
		if($discount==0) $discount='&nbsp;';
		if($in_total==0) $in_total='&nbsp;';
		
		$retval = '<tr>';
		$retval.='<td>'. $roomtypename .'</td>';
		$retval.='<td>'. $doorname .'</td>';
		$retval.='<td>'. $ratename .'</td>';
		$retval.='<td>'. $roomrate .'</td>';
		$retval.='<td>'. $in_ot .'</td>';
		$retval.='<td>'. $discount .'</td>';
		$retval.='<td>'. $in_total .'</td>';
		$retval.='<td nowrap>'. date('m/d H:i',strtotime($row['actual_checkin'])) .'</td>';
		if(floor( $row['status'] )) {
			$retval.='<td nowrap>'. floor( $row['status'] ) .'</td>';
		}else{
			$retval.='<td nowrap>'. date('m/d H:i',strtotime($row['actual_checkout'])) .'</td>';
		}
		if($food==0) $food='&nbsp;';
		if($beer==0) $beer='&nbsp;';
		if($misc==0) $misc='&nbsp;';
		if($adjust==0) $adjust='&nbsp;';
		if($deduct==0) $deduct='&nbsp;';
		if($out_ot==0) $out_ot='&nbsp;';
		if($total==0) $total='&nbsp;';
		
		$retval.='<td>&nbsp;'. $food .'</td>';
		$retval.='<td>&nbsp;'. $beer .'</td>';
		$retval.='<td>&nbsp;'. $misc .'</td>';
		$retval.='<td>&nbsp;'. $adjust .'</td>';
		$retval.='<td>&nbsp;'. $deduct .'</td>';
		$retval.='<td>&nbsp;'. $out_ot .'</td>';
		$retval.='<td>&nbsp;'. $total .'</td>';
		
		$retval.='<td class="debug"><a href="occupancydetails.php?occ='.$row['occupancy_id'].'" target="_blank">'. $row['occupancy_id'] .'</a></td>';
		$retval.= '</tr>';
		return $retval;
	}
	
	public function getCheckoutDetail($startshift,$end,$suser_id,$euser_id)
	{
		$this->start=$startshift;
		$this->end=$end;
		$retval= $this->getReportHeader($this->start,$this->end,$suser_id,$euser_id);
		$retval.=$this->getSpecialDetail();
		$headers=array('Rm_Type','Rm_No','HRS','Rate','Ext','Disc','InTot',
		'ChkIn','ChkOut',
		'Food','Beer','Misc','Adjust','Deduct','OT','Total');
		$retval.='<table class="report">';
		$retval.='<tr>';
		foreach($headers as $header){
			if(is_array($header)) {
				foreach($header as $key=>$value) {
					$retval.="<th>$value</th>";
				}
			}else{
				$retval.="<th>$header</th>";
			}
		}
		$retval.='</tr>';
		
		//$retval.=$this->getWalkupDetail();
		$retval.=$this->getHotelDetail();		
		$retval.='</table>';
		echo  $retval;
	}
	
	public function getCheckoutSummary($startshift,$end,$suser_id,$euser_id)
	{
		$this->start=$startshift;
		$this->end=$end;
		$retval=$this->getReportHeader($this->start,$this->end,$suser_id,$euser_id,'CHECKOUT SUMMARY');
		$retval.='<table class="report">';
		$retval.='<tr>';
		$headers=array('','#TOTAL',$this->rates,'ROOM',
		'OT','FOOD','BEER','MISC','ADJUST','DEDUCT','TOTAL');
		foreach($headers as $header){
			if(is_array($header)) {
				$cleaned = array_unique($header);
				foreach($cleaned as $key=>$value) {
					$retval.="<th>$value</th>";
				}
			}else{
				$retval.="<th>$header</th>";
			}
		}
		$retval.='</tr>';
		$retval.=$this->getWalkupSummary($this->start,$this->end);
		$retval.=$this->getHotelSummary($this->start,$this->end);		
		$retval.="<tr><th colspan='12' style='text-align:right;'>Grand Total:</th><th >$this->grandtotal</th></tr>";
		$retval.='</table>';
		echo $retval;
	
		
	}
	
	public function getWalkupSummary($start,$end)
	{
		$sql  = "select room_type_id, room_type_name 
				 from room_types 
				 where site_id = 1 
				 order by rank 
				 ";
		$res = mysql_query($sql);
		while(list($roomtypeid,$roomtypename)=mysql_fetch_row($res)) {
			$retval.="<tr>";
			$retval.="<td>$roomtypename</td>";
			$allrates = $this->getRateCountByRoomType($roomtypeid,'',$start,$end);
			$this->walkinsummary['allrates']+=$allrates;
			$retval.= '<td>'. $allrates . '</td>';
			foreach($this->rates as $rateid => $ratename) {
				$ratecount = $this->getRateCountByRoomType($roomtypeid,$rateid,$start,$end);
				$this->walkinsummary[$ratename]+=$ratecount;
				$retval.= '<td>'. $ratecount . '</td>';
				
			}
			$room = $this->getTotalRoomCostByRoomType($roomtypeid,$start,$end,array(15),array(),'item_id');
			$ot = $this->getTotalRoomCostByRoomType($roomtypeid,$start,$end,array(16),array(),'item_id');
			$food = $this->getTotalFoodCostByRoomType($roomtypeid,$start,$end,array(),array(17,21),'category_id');
			$beer = $this->getTotalFoodCostByRoomType($roomtypeid,$start,$end,array(17,21),array(),'category_id');
			$misc = $this->getTotalRoomCostByRoomType($roomtypeid,$start,$end,array(),array(2,3),'category_id');
			$adjust = $this->getTotalRoomCostByRoomType($roomtypeid,$start,$end,array(18),array(),'item_id');
			$disc = $this->getTotalRoomCostByRoomType($roomtypeid,$start,$end,array(17),array(),'item_id');
			$deduct = $this->getTotalRoomCostByRoomType($roomtypeid,$start,$end,array(27),array(),'item_id');
			
			$room = $room + $disc;
			$total = $room + $ot  + $deduct + $adjust + $food + $beer + $misc;
			
			$this->walkinsummary['room'] += $room;
			$this->walkinsummary['ot'] += $ot;
			$this->walkinsummary['foot'] += $food;
			$this->walkinsummary['beer'] += $beer;
			$this->walkinsummary['misc'] += $misc;
			$this->walkinsummary['adjust'] += $adjust;
			$this->walkinsummary['deduct'] += $deduct;
			$this->walkinsummary['total'] += $total;
			$retval.= '<td>'. $room . '</td>';
			$retval.= '<td>'. $ot . '</td>';
			$retval.= '<td>'. $food . '</td>';
			$retval.= '<td>'. $beer . '</td>';
			$retval.= '<td>'. $misc . '</td>';
			$retval.= '<td>'. $adjust . '</td>';
			$retval.= '<td>'. $deduct . '</td>';
			$retval.= '<td>'. number_format($total,2) . '</td>';
			$retval.="</tr>";
			
		}
		if(isset($this->walkinsummary)){
			$retval.="<tr class='aggregates'>";
			$retval.='<th>SubTotals:</th>';
			foreach($this->walkinsummary as $key=>$value) {
				$retval.="<th>$value</th>";
			}
			$retval.="</tr>";
		}
		return $retval;
	}
	
	public function getTotalRoomCostByRoomType($roomtypeid,$start,$end,$in=array(),$out=array(),$key='category_id')
	{
		if(count($in)) {
			$arrIn = implode(',',$in);
			$whereIn = " and b.{$key} in ($arrIn) ";
		}
		if(count($out)) {
			$arrOut = implode(',',$out);
			$whereIn = " and b.{$key} not in ($arrOut) ";
		}
		$_sql = "select sum(unit_cost*qty) from occupancy a, room_sales b,  rooms c
			where b.status in ('Paid')
			and a.occupancy_id = b.occupancy_id	
			
			and a.room_id = c.room_id
			$whereIn $whereOut
			and c.room_type_id = '$roomtypeid' ";
		if($start !='') $_sql.=" and a.actual_checkout >= '$start' "; 
		if($end !='') $_sql.=" and a.actual_checkout <= '$end' ";
		$_res = mysql_query($_sql) or die($_sql);
		
		
		list($value) = mysql_fetch_row($_res);
		//if($arrIn=='15') echo $_sql."<hr>";
		return $value;
	}
	
	public function getTotalRoomRateByRoomType()
	{
		
	}
	
	public function getTotalFoodCostByRoomType($roomtypeid,$start,$end,$in=array(),$out=array(),$key='category_id')
	{
		if(count($in)) {
			$arrIn = implode(',',$in);
			$whereIn = " and b.{$key} in ($arrIn) ";
		}
		if(count($out)) {
			$arrOut = implode(',',$out);
			$whereIn = " and b.{$key} not in ($arrOut) ";
		}
		$_sql = "select sum(unit_cost*qty) from occupancy a, fnb_sales b,  rooms c
			where b.status in ('Paid')
			and a.occupancy_id = b.occupancy_id	
			and a.room_id = c.room_id
			$whereIn $whereOut
			and c.room_type_id = '$roomtypeid' ";
		if($start !='') $_sql.=" and a.actual_checkout >= '$start' "; 
		if($end !='') $_sql.=" and a.actual_checkout <= '$end' ";
		$_res = mysql_query($_sql) or die($_sql);
		
		
		list($value) = mysql_fetch_row($_res);
		//if($arrIn=='17') echo $_sql."<hr>";
		return $value;
	}
	
	public function getRateCountByRoomType($roomtypeid,$rateid='',$start,$end)
	{
		$sql = "select count(a.occupancy_id) from  occupancy a,rooms b
		where a.room_id = b.room_id
		and a.actual_checkout >= '$start'
		and a.actual_checkout <= '$end'
		and b.room_type_id = '$roomtypeid'";
		if($rateid!='') $sql .= " and a.rate_id='$rateid' ";
		
		$res = mysql_query($sql);
		$row = mysql_fetch_row($res);
		return $row[0];
	}
	
	public function getHotelSummary($start,$end)
	{
		$sql  = "select room_type_id, room_type_name 
				 from room_types 
				 where site_id = 2 
				 order by rank 
				 ";
		$res = mysql_query($sql);
		$this->hotelsummary=array();
		while(list($roomtypeid,$roomtypename)=mysql_fetch_row($res)) {
			$retval.="<tr>";
			$retval.="<td>$roomtypename</td>";
			$count=array();
			$counttotal=0;
			$arrDuration = array_unique($this->rates);
			foreach($arrDuration as  $duration) {
				//$duration = str_replace(' HRS', '', $ratename);
				$ratecount = (isset($this->summary[$roomtypename][$duration])) ? $this->summary[$roomtypename][$duration] : 0;
				
				$cancel = $this->cancel['hotel'][$roomtypename][$duration];
				$ratecountwithcancel = $ratecount - $cancel;
				$counttotal += $ratecountwithcancel;
				$counts[$duration]=$ratecountwithcancel;
			}
			$retval.= '<td>'. $counttotal . '</td>';
			$this->hotelsummary['allcounttotal']+=$counttotal;
			foreach ($counts as $key => $value) {
				if($value==0) $value='&nbsp;';
				$retval.= '<td>'. $value . '</td>';
				$this->hotelsummary[$key]+=$value;
			}
			$room = (isset($this->summary[$roomtypename]['room'])) ? $this->summary[$roomtypename]['room'] : '';
			$ot = (isset($this->summary[$roomtypename]['ot'])) ? $this->summary[$roomtypename]['ot'] : '';
			$food = (isset($this->summary[$roomtypename]['food'])) ? $this->summary[$roomtypename]['food'] : '';
			$beer = (isset($this->summary[$roomtypename]['beer'])) ? $this->summary[$roomtypename]['beer'] : '';
			$misc = (isset($this->summary[$roomtypename]['misc'])) ? $this->summary[$roomtypename]['misc'] : '';
			$adjust= (isset($this->summary[$roomtypename]['adjust'])) ? $this->summary[$roomtypename]['adjust'] : '';
			$deduct = (isset($this->summary[$roomtypename]['deduct'])) ? $this->summary[$roomtypename]['deduct'] : '';
			$sales_total = (isset($this->summary[$roomtypename]['sales_total'])) ? $this->summary[$roomtypename]['sales_total'] : 0;

			$this->hotelsummary['room']+=$room;
			$this->hotelsummary['ot']+=$ot;
			$this->hotelsummary['food']+=$food;
			$this->hotelsummary['beer']+=$beer;
			$this->hotelsummary['misc']+=$misc;
			$this->hotelsummary['adjust']+=$adjust;
			$this->hotelsummary['deduct']+=$deduct;
			$this->hotelsummary['sales_total']+=$sales_total;
			if($room==0) $room='&nbsp;';
			if($ot==0) $ot='&nbsp;';
			if($food==0) $food='&nbsp;';
			if($beer==0) $beer='&nbsp;';
			if($misc==0) $misc='&nbsp;';
			if($adjust==0) $adjust='&nbsp;';
			if($deduct==0) $deduct='&nbsp;';
			if($sales_total==0) $sales_total='&nbsp;';
			$retval.= '<td>'. $room . '</td>';
			$retval.= '<td>'. $ot . '</td>';
			$retval.= '<td>'. $food . '</td>';
			$retval.= '<td>'. $beer . '</td>';
			$retval.= '<td>'. $misc . '</td>';
			$retval.= '<td>'. $adjust . '</td>';
			$retval.= '<td>'. $deduct . '</td>';
			$retval.= '<td>'. $sales_total . '</td>';
			$retval.= '</tr>';
		}
		
		//lobby, etc
		$retval .= '<tr>';
		$retval.='<td>Lobby/Banquet</td><td></td><td></td><td></td><td></td><td></td><td></td>';
		$retval.='<td>'. $this->totals['specials']['food'] .'</td>';
		$retval.='<td>'. $this->totals['specials']['beer'] .'</td>';
		$retval.='<td>'. $this->totals['specials']['misc'] .'</td>';
		$retval.='<td>'. $this->totals['specials']['adjust'] .'</td>';
		$retval.='<td>'. $this->totals['specials']['discount'] .'</td>';
		$retval.='<td>'. $this->totals['specials']['total'] .'</td>';
		$retval.= '</tr>';
		
		$this->hotelsummary['food'] += $this->totals['specials']['food'] + $this->totals['specials']['discount'] + $this->totals['specials']['deduct'];
		$this->hotelsummary['beer'] += $this->totals['specials']['beer'];
		$this->hotelsummary['misc'] += $this->totals['specials']['misc'];
		$this->hotelsummary['adjust'] += $this->totals['specials']['adjust'];
		$this->hotelsummary['deduct'] += $this->totals['specials']['discount'] + $this->totals['specials']['deduct'];
		$this->hotelsummary['sales_total'] += $this->totals['specials']['total'];
		$this->grandtotal+=$this->totals['specials']['total'];

		if( isset($this->hotelsummary) ){
			$retval.="<tr class='aggregates'>";
			$retval.='<th>SubTotals:</th>';
			foreach($this->hotelsummary as $key=>$value) {
				if($value==0) $value='&nbsp;';
				$retval.="<th>$value</th>";
			}
			$retval.="</tr>";
		}
		
		
		//save to salessummary
		$hr = date('H', strtotime($this->start));
		if($hr == '23' && $this->shiftnum==1) {
			$this->hotelsummary['salesdate'] = date('Y-m-d', strtotime($this->start . "+ 1 day"));
		}elseif($hr=='00' && $this->shiftnum==3) {
			$this->hotelsummary['salesdate'] = date('Y-m-d', strtotime($this->start . "- 1 day"));
		}else{
			$this->hotelsummary['salesdate'] = date('Y-m-d', strtotime($this->start));
		}
		$this->hotelsummary['shiftnum'] = $this->shiftnum;
		$this->hotelsummary['type'] = 2;//hotel
		$this->saveToSummary($this->hotelsummary);

		return $retval;
	}


	
	
	public function getReportHeader($start,$end,$suser_id,$euser_id,$type='CHECKOUT')
	{
		$sql = "select settings_value from settings where id = '1'";
		$res = mysql_query($sql) or die($sql);
		list($value) = mysql_fetch_row($res);
		$ret.="<div class='report'>";
		$ret.= "<b>".strtoupper($value)."</b><br>";
		$ret.= "<b>ROOM $type REPORT</b><br>";
		$ret.= "<b>SHIFT: </b>".$this->getshift($start). "&nbsp;&nbsp;&nbsp;&nbsp;".date("m/d/Y g:i:s A",strtotime($start));
		$ret.= "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>CASHIER: </b>";
		if($suser_id == $euser_id)
		{
			$_sql = "select fullname from users where user_id = '$euser_id'";
			$_res = mysql_query($_sql) or die($_sql);
			list($cashier)=mysql_fetch_row($_res);
			$ret.=$cashier;
		}else
		{
			$_sql = "select fullname from users where user_id = '$suser_id'";
			$_res = mysql_query($_sql)  or die($_sql);
			$ret.=$scashier;
			list($scashier)=mysql_fetch_row($_res);

			$ret.=" - ";

			$_sql = "select fullname from users where user_id = '$euser_id'";
			$_res = mysql_query($_sql)  or die($_sql);
			list($ecashier)=mysql_fetch_row($_res);
			$ret.=$ecashier;
		}
		
		
		$ret.= "<br>";
		$ret.="</div>";
		$ret.= "<br>";

		return $ret;
	}

	public function getCoopSummary($startshift,$end,$suser_id,$euser_id) {
		include_once('acctg/class.baseobject.php');
		include_once('acctg/class.report.php');
$exceptList = implode(',',$this->arrSpecialRooms);
		$sql = "drop table if exists tempcoopdetails;";
		$lastshift=$this->shiftnum - 1 ;
		mysql_query($sql);
		$sql = "

				create table tempcoopdetails
				select distinct a.occupancy_id,a.actual_checkin,a.actual_checkout,
				b.site_id,b.door_name,b.room_id,c.room_type_id, c.room_type_name,c.rank
				,d.rate_id,d.rate_name,d.duration, a.shift_checkin,
				if(actual_checkout='0000-00-00 00:00:00',timestampdiff(HOUR,a.actual_checkin,now())/d.duration,
				if(actual_checkout > '$this->end',timestampdiff(HOUR,a.actual_checkin,'$this->end')/d.duration, '0')) as 'status',
				if(actual_checkout='0000-00-00 00:00:00',timestampdiff(HOUR,a.actual_checkin,now())/d.duration,
				if(actual_checkout > '$this->end',timestampdiff(HOUR,a.actual_checkin,'$this->end')/d.duration, 
				timestampdiff(HOUR,a.actual_checkin,a.actual_checkout)/d.duration)) as 'los',
				if(a.actual_checkout >= '$this->start' and a.actual_checkout <= '$this->end',1,0) as 'checkout'
				from occupancy a, rooms b, room_types c, rates d , room_sales e
				where 
				(
				(a.actual_checkout >= '$this->start' and a.actual_checkout <= '$this->end' )
				or 
				(e.sales_date >='$this->start' and e.sales_date <='$this->end' and a.actual_checkin <> e.sales_date)
				)
				and a.occupancy_id=e.occupancy_id
				and a.room_id = b.room_id
				and b.room_type_id=c.room_type_id
				and a.rate_id=d.rate_id
				and b.site_id=2
				and a.room_id not in ($exceptList)
				";

		mysql_query($sql) or die(mysql_error());
		$newstart = date('Y-m-d H:i:s', strtotime($this->start . '-24 hours'));
		$sql = " select tempcoopdetails.door_name as 'RM No.', room_sales.sales_date as 'Date Ordered', sales_and_services.sas_description as 'Item', room_sales.unit_cost as 'Unit Cost', 
				room_sales.qty as 'Qty', (room_sales.unit_cost*room_sales.qty) as 'Total Cost', room_sales.update_date as 'Date Updated'
				from room_sales, tempcoopdetails, sales_and_services
				where 
				room_sales.item_id=sales_and_services.sas_id
				and tempcoopdetails.occupancy_id=room_sales.occupancy_id
				and room_sales.status='Paid'
				and room_sales.category_id=2
				and room_sales.update_date >= '$this->start' 
				and room_sales.update_date <= '$this->end' ;
				";
		$res = mysql_query($sql) or die($sql);	


		$arrReport = array(
			'title'    => 'Shogun 2 - Cooperative Sales',
			'aggregates'=> array('Total Cost'),
			'subtitle' => date('l, F d, Y',strtotime($startshift )) . '&nbsp;&nbsp;&nbsp;Shift Hrs: '. date('g:i A',strtotime( $startshift)). ' - ' . date('g:i A',strtotime( $end )),
			'sql'	   => $sql	
			);

		$report = new report($arrReport);
		$report->buildReport();
		return $report->html();
	}
	
	public function getCoopSummary_old($startshift,$end,$suser_id,$euser_id) {
		include_once('acctg/class.baseobject.php');
		include_once('acctg/class.report.php');
		$sql= "
		select room_types.room_type_name as 'RM_TYPE', rooms.door_name as 'RM_NO', room_sales.sales_date as 'SALES DATE', 
		occupancy.actual_checkin as 'CHECKIN', occupancy.actual_checkout as 'CHECKOUT', sales_and_services.sas_description as 'ITEM', 
		room_sales.unit_cost as 'UNIT', room_sales.qty as 'QTY', (room_sales.unit_cost * room_sales.qty) as 'TOTAL COST'
		from room_sales, sas_category, sales_and_services, occupancy, rooms, room_types
		where room_sales.category_id=sas_category.sas_cat_id
		and room_sales.item_id=sales_and_services.sas_id
		and room_sales.occupancy_id=occupancy.occupancy_id
		and occupancy.room_id=rooms.room_id
		and rooms.room_type_id=room_types.room_type_id
		and room_sales.sales_date >= '$startshift'
		and room_sales.sales_date <= '$end'
		and occupancy.actual_checkout <= '$end'
		and room_sales.category_id=2
		order by rooms.door_name
		";

		$arrReport = array(
			'title'    => 'Shogun 1 - Cooperative Sales',
			'aggregates'=> array('TOTAL COST'),
			'subtitle' => date('l, F d, Y',strtotime($startshift )) . '&nbsp;&nbsp;&nbsp;Shift Hrs: '. date('g:i A',strtotime( $startshift)). ' - ' . date('g:i A',strtotime( $end )),
			'sql'	   => $sql	
			);

		$report = new report($arrReport);
		$report->buildReport();
		return $report->html();
	}
	
	public function getCOHPerShift($startshift,$end) {
		include_once('acctg/class.baseobject.php');
		include_once('acctg/class.report.php');
		$sql=" drop table if exists shiftsales;";
		mysql_query($sql);
		$sql= "
			create table shiftsales 
				select  room_types.room_type_name, rooms.door_name, fnb_sales.sales_date,occupancy.actual_checkout,'FNB' as category_type,  food_categories.food_category_name as 'category', fnb.fnb_name as 'item', 
				fnb_sales.unit_cost, fnb_sales.qty, (fnb_sales.unit_cost * fnb_sales.qty) as 'total_cost',
				fnb_sales.status, fnb_sales.remarks,  timediff(fnb_sales.sales_date, 
				occupancy.actual_checkin) as 'LOS as of Trxn', occupancy.occupancy_id
				from fnb_sales, food_categories, fnb, occupancy, rooms, room_types
				where fnb_sales.category_id=food_categories.food_category_id
				and fnb_sales.item_id=fnb.fnb_id
				and fnb_sales.occupancy_id=occupancy.occupancy_id
				and occupancy.room_id=rooms.room_id
				and rooms.room_type_id=room_types.room_type_id
				and fnb_sales.sales_date >= '$startshift' 
				and fnb_sales.sales_date <= '$end'
			union
				select room_types.room_type_name, rooms.door_name, room_sales.sales_date, occupancy.actual_checkout,'RoomSales' as category_type, sas_category.sas_cat_name as 'category', sales_and_services.sas_description as 'item', 
				room_sales.unit_cost, room_sales.qty, (room_sales.unit_cost * room_sales.qty) as 'total_cost',
				room_sales.status, room_sales.remarks,  
				timediff(room_sales.sales_date, occupancy.actual_checkin) as 'LOS as of Trxn',
				occupancy.occupancy_id
				from room_sales, sas_category, sales_and_services, occupancy, rooms, room_types
				where room_sales.category_id=sas_category.sas_cat_id
				and room_sales.item_id=sales_and_services.sas_id
				and room_sales.occupancy_id=occupancy.occupancy_id
				and occupancy.room_id=rooms.room_id
				and rooms.room_type_id=room_types.room_type_id
				and room_sales.sales_date >= '$startshift' 
				and room_sales.sales_date <= '$end'
				";
		mysql_query($sql);
		
		
		
		$sql = " select shiftsales.sales_date,shiftsales.unit_cost, shiftsales.qty, 
			shiftsales.category_type, shiftsales.category, shiftsales.item,
			salesreceipts.receipt_date, salesreceipts.occupancy_id,salesreceipts.tendertype,salesreceipts.amount
			from shiftsales left join salesreceipts on 
			salesreceipts.occupancy_id=shiftsales.occupancy_id
			where 			
			salesreceipts.receipt_date >='$startshift' 
			and salesreceipts.receipt_date <='$end' ";

		$arrReport = array(
			'title'    => 'Shogun 2 - Cash On Hand',
			'aggregates'=> array('amount'),
			'subtitle' => date('l, F d, Y',strtotime($startshift )) . '&nbsp;&nbsp;&nbsp;Shift Hrs: '. date('g:i A',strtotime( $startshift)). ' - ' . date('g:i A',strtotime( $end )),
			'sql'	   => $sql	
			);

		$report = new report($arrReport);
		$report->buildReport();
		return $report->html();
	}
	
	public function setPost($bool=true)
	{
		$this->post=$bool;
	}
	
	public function saveToSummary($params=array()) {
		$site = 2; //shogun 2
		//$type = 2; //walkup-1, hotel-2
		foreach($params as $key => $value) {
			${$key} = $value;
		}
		$checksql = "select count(*) from salessummary 
				where salesdate='$salesdate' and shiftnum='$shiftnum' and site='$site' and type='$type' ";
		$res  = mysql_query($checksql);
		$row = mysql_fetch_row($res);
		
		if($row[0]==1) {
			$delsql  = "delete from salessummary where salesdate='$salesdate' and shiftnum='$shiftnum' and site='$site' and type='$type' ";
			mysql_query($delsql);
		}
		
		$sql = "insert into salessummary (salesdate,shiftnum,site,type,guests,roomsales,overtime,food,beer,banquet,misc,date_posted) values (
			'$salesdate','$shiftnum','$site','$type','$allcounttotal','$room','$ot','$food','$beer','$banquet','$misc',now()
			)";
		
		//print_r($params);
		//echo $sql . "<hr>";
		$res = mysql_query($sql);
	}
	
	public function saveToDetailSummary($params=array()) {
		$site = 2; //shogun 2
		//$type = 2; //walkup-1, hotel-2
		
		foreach($params as $key => $value) {
			${$key} = $value;
		}
		
		$checksql = "select count(*) from salesdetailsummary 
				where salesdate='$salesdate' and shiftnum='$shiftnum' and site='$site' and type='$type' and occupancy='$occupancy'				";
		$res  = mysql_query($checksql);
		$row = mysql_fetch_row($res);
		
		if($row[0]==1) {
			$delsql  = "delete from salesdetailsummary where salesdate='$salesdate' and shiftnum='$shiftnum' and site='$site' and type='$type' and occupancy='$occupancy'";
			mysql_query($delsql);
		}
		
		$fsql = "select a.floor_label from floors a, rooms b where a.floor_id=b.floor_id and b.door_name='$doorname'";
		$fres = mysql_query($fsql);
		list($floor)=mysql_fetch_row($fres);
		
		$sql = "insert into salesdetailsummary (salesdate,shiftnum,site,type,occupancy,floor_id, roomtypename, guests,roomsales,overtime,food,beer,banquet,misc,date_posted) values (
			'$salesdate','$shiftnum','$site','$type','$occupancy','$floor','$roomtypename','$allcounttotal','$room','$ot','$food','$beer','$banquet','$misc',now()
			)";
		
		//print_r($params);
		//echo $sql." $floor <hr>";
		$res = mysql_query($sql) or die($sql . mysql_error());
		
		
	}
}
